from openpyxl import load_workbook

from common.LoggerUtil import log


class ExcelUtil:
    def __init__(self, file_name, sheet_name):
        """文件路径和sheet列名称"""
        self.file_name = file_name
        self.sheet_name = sheet_name

        # 根据传入对象获取一个excel文件对象
        self.wb = load_workbook(self.file_name)

    def open(self):
        """sheet对象"""
        # 根据传入sheet列的名称或者下标值获取sheet对象
        if isinstance(self.sheet_name, int):
            self.sheet = self.wb.worksheets[self.sheet_name]
        else:
            self.sheet = self.wb[self.sheet_name]

    def title(self):
        """
        获取标题行
        """
        self.open()
        title = [c.value for c in self.sheet[1]]
        self.wb.close()
        return title

    def read(self, start_row=2, start_col=1):
        """
        遍历读取excel数据内容
        """
        self.open()
        title = [c.value for c in self.sheet[1]]
        self.wb.close()
        data = []
        # 开始遍历行,从第二行开始遍历，
        for row in range(start_row, self.sheet.max_row + 1):
            row_data = []
            for col in range(start_col, self.sheet.max_column + 1):
                row_data.append(self.sheet.cell(row, col).value)

            # 讲表头数组和行数组整合成字典
            row_data_dict = dict(zip(title, row_data))
            # 将整合的字典数据添加到列表
            data.append(row_data_dict)
        log.info(f"开始遍历读取{self.sheet_name}表单的数据{data}")
        return data

    def save(self):
        self.wb.save(self.file_name)
        self.wb.close()

    def write(self, row, col, data):
        self.open()
        self.sheet.cell(row, col).value = data
        log.info(f"向{self.sheet_name}表单中{row}行{col}列写入测试结果为【{data}】")
        self.save()

    def get_cell_value(self, row, col):
        self.open()
        value = self.sheet.cell(row, col).value
        log.info(f"获取{self.sheet_name}表单中第{row}行{col}列的数据【{value}】")
        print(value)


if __name__ == '__main__':
    file_path = r"D:\workspase\python\autoTest1020\data\data.xlsx"
    sheet_index = 1
    sheet_name = "register"
    xls = ExcelUtil(file_path, sheet_name)
    xls.write(4, 5, "测试1")
    xls.get_cell_value(1, 2)
